﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq.SqlClient;
using System.Text;
using EKAABSInformationManagement.Database;

namespace EKAABSInformationManagement.Service
{
    public class employeeService
    {
        private EKAASBIMEntities dbContext;
        public employeeService()
        {
            dbContext = new EKAASBIMEntities();
        }
        public List<employee> getAllEmployeesInBell()
        {
            return dbContext.employee.ToList();
        }
        public List<string> getAllEmpAlias()
        {
            List<employee> L = getAllEmployeesInBell();
            List<string> AliasList = new List<string>();
            foreach (employee e in L)
            {
                AliasList.Add(e.alias);
            }
            return AliasList;
        }
        public List<customerInfo> getCustomerInfo()
        {
            var query = from e in dbContext.employee
                        join u in dbContext.aspnet_Users on e.alias equals u.UserName
                        join t in dbContext.team on e.team equals t.ID
                        //into temp_t
                        //from t_l in temp_t.DefaultIfEmpty()
                        join p in dbContext.position on e.position equals p.ID
                        //into temp_p
                        //from p_l in temp_p.DefaultIfEmpty()
                        join m in dbContext.aspnet_Membership on u.UserId equals m.UserId
                        select new customerInfo
                        {
                            UserName = e.alias,
                            Position = p.positionAlias,
                            Team = t.teamAlias,
                            Email = m.Email,
                            Role = u.aspnet_Roles.FirstOrDefault().RoleName,
                            startDate = e.startDate
                        };
            return query.ToList();
        }
        public List<employee> getEmployeesInBellBy(string alias, int team, int position, DateTime startDate)
        {
            var query = from e in dbContext.employee
                        where e.alias == alias && e.team == team && e.position == position && e.startDate == startDate
                        select e;
            return query.ToList();
        }
        public List<employee> getEmployeesInBellBy(string alias, int? team)
        {
            var query = from e in dbContext.employee
                        where e.alias == alias && e.team == team
                        select e;
            return query.ToList();
        }
        public employee getEmployeesInBellBy(string alias)
        {
            var query = from e in dbContext.employee
                        where e.alias == alias
                        select e;
            return query.FirstOrDefault();
        }
        public customerInfo getCustomerInfoByAlias(string alias)
        {
            var query = from e in dbContext.employee
                        join t in dbContext.team on e.team equals t.ID
                        join p in dbContext.position on e.position equals p.ID
                        join u in dbContext.aspnet_Users on e.alias equals u.UserName
                        join m in dbContext.aspnet_Membership on u.UserId equals m.UserId
                        //join a in dbContext.asset on e.alias equals a.belongto into tempAsset
                        //from a_l in tempAsset.DefaultIfEmpty()
                        //join acc in dbContext.accountInBell on e.alias equals acc.belongto into tempAcc
                        //from acc_l in tempAcc.DefaultIfEmpty()
                        //join c in dbContext.cardInBell on e.alias equals c.belongto into tempCard
                        //from c_l in tempCard.DefaultIfEmpty()
                        //join n in dbContext.network on a_l.assetID equals n.assetID into tempNetwork
                        //from n_l in tempNetwork.DefaultIfEmpty()
                        where e.alias == alias
                        select new customerInfo
                        {
                            UserName = e.alias,
                            Position = p.positionAlias,
                            Team = t.teamAlias,
                            Email = m.Email,
                            Role = u.aspnet_Roles.FirstOrDefault().RoleName
                        };
            return query.FirstOrDefault();
        }
        public void insertIntoEmployees(string myAlias, string myTeam, string myPosition, DateTime? date)
        {
            int teamID = default(int);
            int positionID = default(int);
            Int32.TryParse(myTeam, out teamID);
            Int32.TryParse(myPosition, out positionID);
            if (teamID == 0) teamID = getTeamID(myTeam);
            if (positionID == 0) positionID = getPositionID(myPosition);
            var myEmp = new employee
                {
                    alias = myAlias,
                    team = teamID,
                    position = positionID,
                    startDate = date
                };
            dbContext.employee.AddObject(myEmp);
            dbContext.SaveChanges();
        }
        public List<team> getAllTeam()
        {
            return dbContext.team.ToList();
        }
        public List<position> getAllPosition()
        {
            return dbContext.position.ToList();
        }
        public int getTeamID(string myTeam)
        {
            int teamID = default(int);
            if (string.IsNullOrEmpty(myTeam) != false) throw new Exception("所选小组不存在！");
            var teamQuery = dbContext.team.Where(o => o.teamAlias == myTeam).FirstOrDefault();
            if (teamQuery == null)
            {
                var tempTeam = new team { teamAlias = myTeam };
                dbContext.team.AddObject(tempTeam);
                dbContext.SaveChanges();
                teamID = tempTeam.ID;
            }
            else
            {
                teamID = teamQuery.ID;
            }

            return teamID;
        }
        public int getPositionID(string myPosition)
        {
            int positionID = default(int);
            if (string.IsNullOrEmpty(myPosition) != false) throw new Exception("所选职位不存在！");

            var positionQuery = dbContext.position.Where(o => o.positionAlias == myPosition).FirstOrDefault();
            if (positionQuery == null)
            {
                var tempPosition = new position { positionAlias = myPosition };
                dbContext.position.AddObject(tempPosition);
                dbContext.SaveChanges();
                positionID = tempPosition.ID;
            }
            else
            {
                positionID = positionQuery.ID;
            }

            return positionID;
        }
        public void updateEmployee(string alias, string myTeam, string myPosition)
        {
            int teamID = default(int);
            int positionID = default(int);
            Int32.TryParse(myTeam, out teamID);
            Int32.TryParse(myPosition, out positionID);
            employee currentEmp = dbContext.employee.SingleOrDefault(o => o.alias == alias);
            if (currentEmp == null) return;
            if (teamID == 0) teamID = getTeamID(myTeam);
            if (positionID == 0) positionID = getPositionID(myPosition);

            currentEmp.team = teamID;
            currentEmp.position = positionID;

            dbContext.SaveChanges();
        }
        public void deleteEmployeeByAlias(string alias)
        {
            var d = dbContext.employee.SingleOrDefault(o => o.alias == alias);
            if (d != null)
            {
                dbContext.DeleteObject(d);
                dbContext.SaveChanges();
            }
        }
        public void deleteRelatedAssetsByAlias(string alias)
        {
            var assetsList = dbContext.asset.Where(o => o.belongto == alias).ToList();
            if (assetsList.Count() > 0)
            {
                foreach (var a in assetsList)
                {
                    a.belongto = "暂无owner";
                }
                dbContext.SaveChanges();
            }
        }
        public void deleteRelatedAccountByAlias(string alias)
        {
            var d = dbContext.accountInBell.SingleOrDefault(o => o.belongto == alias);
            if (d != null)
            {
                dbContext.DeleteObject(d);
                dbContext.SaveChanges();
            }
        }
        public void deleteRelatedCardByAlias(string alias)
        {
            var d = dbContext.cardInBell.SingleOrDefault(o => o.belongto == alias);
            if (d != null)
            {
                dbContext.DeleteObject(d);
                dbContext.SaveChanges();
            }
        }
    }
}
